package com.crcc.common.util;

import com.crcc.common.constant.CommonConsts;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class ExcelUtil {

    public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook workbook) {
        // 创建一个HSSFWorkbook，对应一个Excel文件
        if (workbook == null) {
            workbook = new HSSFWorkbook();
        }
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 在sheet中添加表头第0行
        HSSFRow row = sheet.createRow(0);
        // 创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
//        cellStyle.setWrapText(true);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 声明列对象
        HSSFCell cell = null;
        // 创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(cellStyle);
        }
        // 创建内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < values[i].length; j++) {
                cell = row.createCell(j);
                // 将内容按顺序赋给对应的列对象
                cell.setCellValue(values[i][j]);
                cell.setCellStyle(cellStyle);
            }
        }
        for (int i = 0; i < title.length; i++) {
            // 调整每一列宽度
            sheet.autoSizeColumn((short) i);
            // 解决自动设置列宽中文失效的问题
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
//            sheet.setColumnWidth(i, 1000);
        }
        return workbook;

    }

    public static HSSFWorkbook getHSSFWorkbookDoor(String sheetName, String[] title, String[][] values, HSSFWorkbook workbook) {
        // 创建一个HSSFWorkbook，对应一个Excel文件
        if (workbook == null) {
            workbook = new HSSFWorkbook();
        }
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 在sheet中添加表头第0行
        HSSFRow row = sheet.createRow(0);
        // 创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //自动换行
        HSSFCellStyle cellStyleNew = workbook.createCellStyle();
        cellStyleNew.setWrapText(true);
        cellStyleNew.setAlignment(HorizontalAlignment.CENTER);
        // 声明列对象
        HSSFCell cell = null;
        // 创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            if (i == 7) {
                cell.setCellStyle(cellStyleNew);
            } else {
                cell.setCellStyle(cellStyle);
            }
        }
        // 创建内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < values[i].length; j++) {
                cell = row.createCell(j);
                // 将内容按顺序赋给对应的列对象
                cell.setCellValue(values[i][j]);
                if (j == 7) {
                    cell.setCellStyle(cellStyleNew);
                } else {
                    cell.setCellStyle(cellStyle);
                }
                int colWidth = sheet.getColumnWidth(j) * 2;
                /*if (colWidth < 255 * 256) {
                    sheet.setColumnWidth(j, colWidth < 3000 ? 3000 : colWidth);
                } else {
                    sheet.setColumnWidth(j, 6000);
                }*/
                sheet.autoSizeColumn((short) i);
                sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 12);
            }
        }
        return workbook;

    }

    public static HSSFWorkbook getHSSFWorkbookTitle(String sheetName, int sheetNum, String sheetTitle, String[] title, String[] title1, String[][] values, HSSFWorkbook workbook) {
        // 创建一个HSSFWorkbook，对应一个Excel文件
        if (workbook == null) {
            workbook = new HSSFWorkbook();
        }
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        //workbook.setSheetName(sheetNum, sheetTitle);
        // 在sheet中添加表头第0行
        HSSFRow row = sheet.createRow(0);
        HSSFRow row1 = sheet.createRow(1);
        // 创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 声明列对象
        HSSFCell cell = null;
        // 项目
        for (int i = 0; i < title1.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title1[i]);
            cell.setCellStyle(cellStyle);
        }
        // 数据列
        for (int i = 0; i < title.length; i++) {
            cell = row1.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(cellStyle);
        }
        // 创建内容
        for (int i = 0; i < values.length; i++) {
            row1 = sheet.createRow(i + 1);
            for (int j = 0; j < values[i].length; j++) {
                // 将内容按顺序赋给对应的列对象
                row1.createCell(j).setCellValue(values[i][j]);
            }
        }
        for (int i = 0; i < title.length; i++) {
            // 调整每一列宽度
            sheet.autoSizeColumn((short) i);
            // 解决自动设置列宽中文失效的问题
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
        }
        return workbook;

    }

    /**
     * 考勤月统计excle
     *
     * @param sheetName
     * @param title
     * @param title1
     * @param title2
     * @param title3
     * @param title4
     * @param values
     * @param workbook
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbookRegionYear(String sheetName, String[] title, String[] title1, String[] title2, String[] title3, String[] title4, String[][] values, HSSFWorkbook workbook) {
        // 创建一个HSSFWorkbook，对应一个Excel文件
        if (workbook == null) {
            workbook = new HSSFWorkbook();
        }
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 在sheet中添加表头第0行
        // HSSFRow row = sheet.createRow(0);
        // 创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 声明列对象
        HSSFCell cell = null;
        // 创建标题
        for (int i = 0; i < title.length; i++) {
            HSSFCellStyle cellStyle1 = workbook.createCellStyle();
            cellStyle1.setAlignment(HorizontalAlignment.CENTER);
            HSSFFont font2 = workbook.createFont();
            font2.setFontName("仿宋_GB2312");
            font2.setFontHeightInPoints((short) 28);  //字体大小
            cellStyle1.setFont(font2);
            HSSFRow row = sheet.createRow(i);//创建表格行
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(cellStyle1);
            row.setHeightInPoints(30);
        }
        // 创建标题
        for (int i = 0; i < title1.length; i++) {
            HSSFRow row = sheet.createRow(i + 1);//创建表格行
            cell = row.createCell(i);
            cell.setCellValue(title1[i]);
            cell.setCellStyle(HSSFCellStyle(workbook));
            row.setHeightInPoints(18);
        }
        // 创建标题
        HSSFRow row2 = sheet.createRow(2);//创建表格行
        for (int i = 0; i < title2.length; i++) {
            if (i > 2) {
//                cell = row2.createCell(i+29);
                cell = row2.createCell(i + title3.length - 1);
            } else {
                cell = row2.createCell(i);
            }
            cell.setCellValue(title2[i]);
            cell.setCellStyle(cellStyle);
        }
        // 创建标题
        HSSFRow row3 = sheet.createRow(3);//创建表格行
        for (int i = 0; i < title3.length; i++) {
            cell = row3.createCell(i + 2);
            cell.setCellValue(title3[i]);
            cell.setCellStyle(cellStyle);
        }
        // 创建标题
        HSSFRow row4 = sheet.createRow(values.length + 4);//创建表格行
        for (int i = 0; i < title4.length; i++) {
            cell = row4.createCell(i);
            cell.setCellValue(title4[i]);
            cell.setCellStyle(HSSFCellStyle(workbook));
            row4.setHeightInPoints(16);
        }
        //合并单元格--标题
        CellRangeAddress region = new CellRangeAddress(0, // first row
                0, // last row
                0, // first column
//                32 // last column
                title3.length + 2
        );
        sheet.addMergedRegion(region);
        //合并单元格--标题
        CellRangeAddress region2 = new CellRangeAddress(1, // first row
                1, // last row
                0, // first column
//                32 // last column
                title3.length + 2
        );
        sheet.addMergedRegion(region2);
        //合并单元格--日期
        CellRangeAddress region3 = new CellRangeAddress(2, // first row
                2, // last row
                2, // first column
//                31 // last column
                title3.length + 1
        );
        sheet.addMergedRegion(region3);
        //合并单元格-序号
        CellRangeAddress region4 = new CellRangeAddress(2, // first row
                3, // last row
                0, // first column
                0 // last column
        );
        sheet.addMergedRegion(region4);
        //合并单元格-姓名
        CellRangeAddress region5 = new CellRangeAddress(2, // first row
                3, // last row
                1, // first column
                1 // last column
        );
        sheet.addMergedRegion(region5);
        //合并单元格-合计
        CellRangeAddress region6 = new CellRangeAddress(2, // first row
                3, // last row
//                32, // first column
//                32 // last column
                title3.length + 2,
                title3.length + 2
        );
        sheet.addMergedRegion(region6);
        //合并单元格-签字
        CellRangeAddress region8 = new CellRangeAddress(values.length + 4, // first row
                values.length + 4, // last row
                0, // first column
                32 // last column
        );
        sheet.addMergedRegion(region8);
        // 创建内容
        for (int i = 0; i < values.length; i++) {
            HSSFRow row = sheet.createRow(i + 4);
            for (int j = 0; j < values[i].length; j++) {
                cell = row.createCell(j);
                // 将内容按顺序赋给对应的列对象
                cell.setCellValue(values[i][j]);
                cell.setCellStyle(cellStyle);
            }
        }
        for (int i = 0; i < title.length; i++) {
            // 调整每一列宽度
            sheet.autoSizeColumn((short) i);
            // 解决自动设置列宽中文失效的问题
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
        }
        return workbook;
    }


    /**
     * 考勤excle
     *
     * @param sheetName
     * @param title
     * @param title1
     * @param title2
     * @param title3
     * @param title4
     * @param title5
     * @param values
     * @param workbook
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbookRegion(String sheetName, String[] title, String[] title1, String[] title2, String[] title3, String[] title4, String[] title5, String[][] values, HSSFWorkbook workbook) {
        // 创建一个HSSFWorkbook，对应一个Excel文件
        if (workbook == null) {
            workbook = new HSSFWorkbook();
        }
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 在sheet中添加表头第0行
        // HSSFRow row = sheet.createRow(0);
        // 创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 声明列对象
        HSSFCell cell = null;
        // 创建标题
        for (int i = 0; i < title.length; i++) {
            HSSFCellStyle cellStyle1 = workbook.createCellStyle();
            cellStyle1.setAlignment(HorizontalAlignment.CENTER);
            HSSFFont font2 = workbook.createFont();
            font2.setFontName("仿宋_GB2312");
            font2.setFontHeightInPoints((short) 28);  //字体大小
            cellStyle1.setFont(font2);
            HSSFRow row = sheet.createRow(i);//创建表格行
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(cellStyle1);
            row.setHeightInPoints(30);
        }
        // 创建标题
        for (int i = 0; i < title1.length; i++) {
            HSSFRow row = sheet.createRow(i + 1);//创建表格行
            cell = row.createCell(i);
            cell.setCellValue(title1[i]);
            cell.setCellStyle(HSSFCellStyle(workbook));
            row.setHeightInPoints(18);
        }
        // 创建标题
        HSSFRow row2 = sheet.createRow(2);//创建表格行
        for (int i = 0; i < title2.length; i++) {
            if (i > 2) {
//                cell = row2.createCell(i+29);
                cell = row2.createCell(i + title3.length - 1);
            } else {
                cell = row2.createCell(i);
            }
            cell.setCellValue(title2[i]);
            cell.setCellStyle(cellStyle);
        }
        // 创建标题
        HSSFRow row3 = sheet.createRow(3);//创建表格行
        for (int i = 0; i < title3.length; i++) {
            cell = row3.createCell(i + 2);
            cell.setCellValue(title3[i]);
            cell.setCellStyle(cellStyle);
        }
        // 创建标题
        HSSFRow row4 = sheet.createRow(values.length + 4);//创建表格行
        for (int i = 0; i < title4.length; i++) {
            cell = row4.createCell(i);
            cell.setCellValue(title4[i]);
            cell.setCellStyle(HSSFCellStyle(workbook));
            row4.setHeightInPoints(16);
        }
        // 创建文本尾部
        HSSFRow row5 = sheet.createRow(values.length + 5);//创建表格行
        for (int i = 0; i < title5.length; i++) {
            HSSFCellStyle cellStyle1 = workbook.createCellStyle();
            cellStyle1.setAlignment(HorizontalAlignment.LEFT);
            if (i == 0) {
                cell = row5.createCell(i);
            } else if (i == 1) {
                cell = row5.createCell(i + 20);
            } else {
                cell = row5.createCell(i + 27);
            }
            cellStyle1.setWrapText(true);//先设置为自动换行 数据中"\r\n为显示换行"
            cell.setCellValue(title5[i]);
            cell.setCellStyle(cellStyle1);
            row5.setHeightInPoints(40);
            HSSFFont font2 = workbook.createFont();
            font2.setFontName("仿宋_GB2312");
            font2.setFontHeightInPoints((short) 12);  //字体大小
            cellStyle1.setFont(font2);
        }
        //合并单元格--标题
        CellRangeAddress region = new CellRangeAddress(0, // first row
                0, // last row
                0, // first column
                //33 // last column
                33
        );
        sheet.addMergedRegion(region);
        //合并单元格--标题
        CellRangeAddress region2 = new CellRangeAddress(1, // first row
                1, // last row
                0, // first column
                //33 // last column
                33
        );
        sheet.addMergedRegion(region2);
        //合并单元格--日期
        CellRangeAddress region3 = new CellRangeAddress(2, // first row
                2, // last row
                2, // first column
                //31 // last column
                title3.length + 1
        );
        sheet.addMergedRegion(region3);
        //合并单元格-序号
        CellRangeAddress region4 = new CellRangeAddress(2, // first row
                3, // last row
                0, // first column
                0 // last column
        );
        sheet.addMergedRegion(region4);
        //合并单元格-姓名
        CellRangeAddress region5 = new CellRangeAddress(2, // first row
                3, // last row
                1, // first column
                1 // last column
        );
        sheet.addMergedRegion(region5);
        //合并单元格-合计
        CellRangeAddress region6 = new CellRangeAddress(2, // first row
                3, // last row
//                32, // first column
//                32 // last column
                title3.length + 2,
                title3.length + 2
        );
        sheet.addMergedRegion(region6);
        //合并单元格-签字
        CellRangeAddress region7 = new CellRangeAddress(2, // first row
                3, // last row
//                33, // first column
//                33 // last column
                title3.length + 3,
                title3.length + 3
        );
        sheet.addMergedRegion(region7);
        //合并单元格-签字
        CellRangeAddress region8 = new CellRangeAddress(values.length + 4, // first row
                values.length + 4, // last row
                0, // first column
                // 32 // last column
                33
        );
        sheet.addMergedRegion(region8);
        //合并单元格-签字
        CellRangeAddress region9 = new CellRangeAddress(values.length + 5, // first row
                values.length + 7, // last row
                0, // first column
                20 // last column
        );
        sheet.addMergedRegion(region9);
        CellRangeAddress region12 = new CellRangeAddress(values.length + 5, // first row
                values.length + 7, // last row
                21, // first column
                28 // last column
        );
        sheet.addMergedRegion(region12);
        //合并单元格-签字
        CellRangeAddress region13 = new CellRangeAddress(values.length + 5, // first row
                values.length + 7, // last row
                29, // first column
                //33 // last column
                33
        );
        sheet.addMergedRegion(region13);
        // 创建内容
        for (int i = 0; i < values.length; i++) {
            HSSFRow row = sheet.createRow(i + 4);
            for (int j = 0; j < values[i].length; j++) {
                cell = row.createCell(j);
                // 将内容按顺序赋给对应的列对象
                cell.setCellValue(values[i][j]);
                cell.setCellStyle(cellStyle);
            }
        }
        for (int i = 0; i < title.length; i++) {
            // 调整每一列宽度
            sheet.autoSizeColumn((short) i);
            // 解决自动设置列宽中文失效的问题
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
        }
        return workbook;
    }


    /**
     * 工资excle
     *
     * @param sheetName
     * @param title
     * @param title1
     * @param title2
     * @param title3
     * @param values
     * @param workbook
     * @return
     */
    public static HSSFWorkbook getHSSNewSalaryRegion(int num, String sheetName, String[] title, String[] title1, String[] title2, String[] title3, String[] title4, String[][] values, HSSFWorkbook workbook) {
        // 创建一个HSSFWorkbook，对应一个Excel文件
        if (workbook == null) {
            workbook = new HSSFWorkbook();
        }
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 在sheet中添加表头第0行
        // HSSFRow row = sheet.createRow(0);
        // 创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setWrapText(true);
        cellStyle.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);
        //垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 声明列对象
        HSSFCell cell = null;
        // 创建标题
        for (int i = 0; i < title.length; i++) {
            HSSFCellStyle cellStyle1 = workbook.createCellStyle();
            cellStyle1.setAlignment(HorizontalAlignment.CENTER);
            HSSFFont font2 = workbook.createFont();
            font2.setFontName("仿宋_GB2312");
            font2.setFontHeightInPoints((short) 28);  //字体大小
            cellStyle1.setFont(font2);
            HSSFRow row = sheet.createRow(i);//创建表格行
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(cellStyle1);
            row.setHeightInPoints(65);
        }
        // 创建标题
        for (int i = 0; i < title1.length; i++) {
            HSSFRow row = sheet.createRow(i + 1);//创建表格行
            cell = row.createCell(i);
            cell.setCellValue(title1[i]);
            cell.setCellStyle(HSSFCellStyle(workbook));
            row.setHeightInPoints(30);
        }

        // 创建标题
        int not = 0;
        int in = 0;
        HSSFRow row3 = sheet.createRow(2);//创建表格行
        for (int i = 0; i < title3.length; i++) {
            if ("社会保险".equals(title3[i])) {
                not = i + title2.length;
                in = i;
                cell = row3.createCell(i);
                cell.setCellValue(title3[i]);
                cell.setCellStyle(cellStyle);
                CellRangeAddress region5 = new CellRangeAddress(2, 2, i, i + title2.length - 1);
                sheet.addMergedRegion(region5);
                HSSFRow row2 = sheet.createRow(3);//创建表格行
                for (int j = 0; j < title2.length; j++) {
                    cell = row2.createCell(j + i);
                    cell.setCellValue(title2[j]);
                    cell.setCellStyle(cellStyle);
                }
            } else {
                cell = row3.createCell(not);
                cell.setCellValue(title3[i]);
                cell.setCellStyle(cellStyle);
                sheet.addMergedRegion(new CellRangeAddress(2, 3, not, not));
                not = not + 1;
            }
        }

        // 创建标题
        HSSFRow row4 = sheet.createRow(values.length + 4);//创建表格行
        for (int i = 0; i < title4.length; i++) {
            cell = row4.createCell(i);
            cell.setCellValue(title4[i]);
            cell.setCellStyle(HSSFCellStyle(workbook));
        }
        //合并单元格--标题
        CellRangeAddress region = new CellRangeAddress(0, // first row
                0, // last row
                0, // first column
                not // last column
        );
        sheet.addMergedRegion(region);
        //合并单元格--标题
        CellRangeAddress region2 = new CellRangeAddress(1, // first row
                1, // last row
                0, // first column
                not // last column
        );
        sheet.addMergedRegion(region2);
        //合并单元格-底部签字
        CellRangeAddress region6 = new CellRangeAddress(values.length + 4, // first row
                values.length + 7, // last row
                0, // first column
                not // last column
        );
        sheet.addMergedRegion(region6);
        //合并单元格-隐藏信息
        sheet.addMergedRegion(new CellRangeAddress(2, 3, not, not));
        // 创建内容
        for (int i = 0; i < values.length; i++) {
            HSSFRow row = sheet.createRow(i + 4);
            for (int j = 0; j < values[i].length; j++) {
                cell = row.createCell(j);
                // 将内容按顺序赋给对应的列对象
                cell.setCellValue(values[i][j]);
                cell.setCellStyle(cellStyle);
                if (j == not) {
                    sheet.setColumnWidth(j, 0);
                }
            }
        }
        for (int i = 0; i < title3.length; i++) {
            // 调整每一列宽度
//            sheet.autoSizeColumn((short) i);
            // 解决自动设置列宽中文失效的问题
//            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
            if (i == 2) {
                sheet.setColumnWidth(i, 6000);
            } else {
                sheet.setColumnWidth(i, 2000);
            }
        }
        return workbook;
    }

    public static HSSFWorkbook getHSSSalaryRegion(int num, String sheetName, String[] title, String[] title1, String[] title2, String[] title3, String[] title4, String[][] values, HSSFWorkbook workbook) {
        // 创建一个HSSFWorkbook，对应一个Excel文件
        if (workbook == null) {
            workbook = new HSSFWorkbook();
        }
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 在sheet中添加表头第0行
        // HSSFRow row = sheet.createRow(0);
        // 创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 声明列对象
        HSSFCell cell = null;
        // 创建标题
        for (int i = 0; i < title.length; i++) {
            HSSFCellStyle cellStyle1 = workbook.createCellStyle();
            cellStyle1.setAlignment(HorizontalAlignment.CENTER);
            HSSFFont font2 = workbook.createFont();
            font2.setFontName("仿宋_GB2312");
            font2.setFontHeightInPoints((short) 28);  //字体大小
            cellStyle1.setFont(font2);
            HSSFRow row = sheet.createRow(i);//创建表格行
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(cellStyle1);
            row.setHeightInPoints(30);
        }
        // 创建标题
        for (int i = 0; i < title1.length; i++) {
            HSSFRow row = sheet.createRow(i + 1);//创建表格行
            cell = row.createCell(i);
            cell.setCellValue(title1[i]);
            cell.setCellStyle(HSSFCellStyle(workbook));
            row.setHeightInPoints(18);
        }
        // 创建标题
        HSSFRow row2 = sheet.createRow(3);//创建表格行
        for (int i = 0; i < title2.length; i++) {
            cell = row2.createCell(i + 11);
            cell.setCellValue(title2[i]);
            cell.setCellStyle(cellStyle);
        }
        // 创建标题
        HSSFRow row3 = sheet.createRow(2);//创建表格行
        for (int i = 0; i < title3.length; i++) {
            if (i > 11) {
                cell = row3.createCell(i + 4);
            } else {
                cell = row3.createCell(i);
            }
            cell.setCellValue(title3[i]);
            cell.setCellStyle(cellStyle);
        }
        // 创建标题
        HSSFRow row4 = sheet.createRow(values.length + 4);//创建表格行
        for (int i = 0; i < title4.length; i++) {
            cell = row4.createCell(i);
            cell.setCellValue(title4[i]);
            cell.setCellStyle(HSSFCellStyle(workbook));
        }
        //合并单元格--标题
        CellRangeAddress region = new CellRangeAddress(0, // first row
                0, // last row
                0, // first column
                26 // last column
        );
        sheet.addMergedRegion(region);
        //合并单元格--标题
        CellRangeAddress region2 = new CellRangeAddress(1, // first row
                1, // last row
                0, // first column
                26 // last column
        );
        sheet.addMergedRegion(region2);
        //合并单元格-姓名
        CellRangeAddress region5 = new CellRangeAddress(2, // first row
                2, // last row
                11, // first column
                15 // last column
        );
        sheet.addMergedRegion(region5);
        //合并单元格-底部签字
        CellRangeAddress region6 = new CellRangeAddress(values.length + 4, // first row
                values.length + 7, // last row
                0, // first column
                26 // last column
        );
        sheet.addMergedRegion(region6);
        //合并单元格-序号
        sheet.addMergedRegion(new CellRangeAddress(2, // first row
                3, // last row
                0, // first column
                0 // last column
        ));

        //合并单元格-姓名
        sheet.addMergedRegion(new CellRangeAddress(2, // first row
                3, // last row
                1, // first column
                1 // last column
        ));

        //合并单元格-身份证
        sheet.addMergedRegion(new CellRangeAddress(2, // first row
                3, // last row
                2, // first column
                2 // last column
        ));
        //合并单元格-卡号
        sheet.addMergedRegion(new CellRangeAddress(2, // first row
                3, // last row
                3, // first column
                3 // last column
        ));
        //合并单元格-开户行
        sheet.addMergedRegion(new CellRangeAddress(2, // first row
                3, // last row
                4, // first column
                4 // last column
        ));
        //合并单元格-省份
        sheet.addMergedRegion(new CellRangeAddress(2, // first row
                3, // last row
                5, // first column
                5 // last column
        ));
        //合并单元格-城市
        sheet.addMergedRegion(new CellRangeAddress(2, // first row
                3, // last row
                6, // first column
                6 // last column
        ));
        //合并单元格-岗位
        sheet.addMergedRegion(new CellRangeAddress(2, // first row
                3, // last row
                7, // first column
                7 // last column
        ));
        //合并单元格-考勤天数
        sheet.addMergedRegion(new CellRangeAddress(2, // first row
                3, // last row
                8, // first column
                8 // last column
        ));
        //合并单元格-日工资
        sheet.addMergedRegion(new CellRangeAddress(2, // first row
                3, // last row
                9, // first column
                9 // last column
        ));
        //合并单元格-实际出勤天数
        sheet.addMergedRegion(new CellRangeAddress(2, // first row
                3, // last row
                10, // first column
                10 // last column
        ));
        //合并单元格-满勤
        sheet.addMergedRegion(new CellRangeAddress(2, // first row
                3, // last row
                16, // first column
                16 // last column
        ));
        //合并单元格-加班费
        sheet.addMergedRegion(new CellRangeAddress(2, // first row
                3, // last row
                17, // first column
                17 // last column
        ));
        //合并单元格-伙食费
        sheet.addMergedRegion(new CellRangeAddress(2, // first row
                3, // last row
                18, // first column
                18 // last column
        ));
        //合并单元格-补发工资
        sheet.addMergedRegion(new CellRangeAddress(2, // first row
                3, // last row
                19, // first column
                19 // last column
        ));
        //合并单元格-本月应发
        sheet.addMergedRegion(new CellRangeAddress(2, // first row
                3, // last row
                20, // first column
                20 // last column
        ));
        //合并单元格-扣税基数
        sheet.addMergedRegion(new CellRangeAddress(2, // first row
                3, // last row
                21, // first column
                21 // last column
        ));
        //合并单元格-扣税金额
        sheet.addMergedRegion(new CellRangeAddress(2, // first row
                3, // last row
                22, // first column
                22 // last column
        ));
        //合并单元格-扣除工资金额
        sheet.addMergedRegion(new CellRangeAddress(2, // first row
                3, // last row
                23, // first column
                23 // last column
        ));
        //合并单元格-实发金额
        sheet.addMergedRegion(new CellRangeAddress(2, // first row
                3, // last row
                24, // first column
                24 // last column
        ));
        //合并单元格-签字
        sheet.addMergedRegion(new CellRangeAddress(2, // first row
                3, // last row
                25, // first column
                25 // last column
        ));
        //合并单元格-备注
        sheet.addMergedRegion(new CellRangeAddress(2, // first row
                3, // last row
                26, // first column
                26 // last column
        ));
        if (num == CommonConsts.ONE) {
            //合并单元格-错误信息
            sheet.addMergedRegion(new CellRangeAddress(2, // first row
                    3, // last row
                    28, // first column
                    28 // last column
            ));
        }
        //合并单元格-隐藏信息
        sheet.addMergedRegion(new CellRangeAddress(2, // first row
                3, // last row
                27, // first column
                27 // last column
        ));
        // 创建内容
        for (int i = 0; i < values.length; i++) {
            HSSFRow row = sheet.createRow(i + 4);
            for (int j = 0; j < values[i].length; j++) {
                cell = row.createCell(j);
                // 将内容按顺序赋给对应的列对象
                cell.setCellValue(values[i][j]);
                cell.setCellStyle(cellStyle);
                if (j > 26) {
                    sheet.setColumnWidth(j, 0);
                }
            }
        }
        for (int i = 0; i < title.length; i++) {
            // 调整每一列宽度
            sheet.autoSizeColumn((short) i);
            // 解决自动设置列宽中文失效的问题
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
        }
        return workbook;
    }

    /**
     * 人员excle
     *
     * @param sheetName
     * @param title
     * @param title1
     * @param title2
     * @param title3
     * @param values
     * @param workbook
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbookInfo(String sheetName, String[] title, String[] title1, String[] title2, String[] title3, String[][] values, HSSFWorkbook workbook) {
        // 创建一个HSSFWorkbook，对应一个Excel文件
        if (workbook == null) {
            workbook = new HSSFWorkbook();
        }
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 在sheet中添加表头第0行
        // HSSFRow row = sheet.createRow(0);
        // 创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 声明列对象
        HSSFCell cell = null;
        // 创建标题
        for (int i = 0; i < title.length; i++) {
            HSSFCellStyle cellStyle1 = workbook.createCellStyle();
            cellStyle1.setAlignment(HorizontalAlignment.CENTER);
            HSSFFont font2 = workbook.createFont();
            font2.setFontName("仿宋_GB2312");
            font2.setFontHeightInPoints((short) 11);  //字体大小
            cellStyle1.setFont(font2);
            HSSFRow row = sheet.createRow(i);//创建表格行
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(cellStyle1);
            row.setHeightInPoints(20);
        }
        // 序号 务工人员基本信息 入场基本信息
        HSSFRow row1 = sheet.createRow(1);//创建表格行
        for (int i = 0; i < title1.length; i++) {
            if (i > 1) {
                cell = row1.createCell(11);
            } else {
                cell = row1.createCell(i);
            }
            cell.setCellValue(title1[i]);
            cell.setCellStyle(cellStyle);
        }
        // 序号 务工人员基本信息 入场基本信息
        HSSFRow row2 = sheet.createRow(2);//创建表格行
        for (int i = 0; i < title2.length; i++) {
            cell = row2.createCell(i + 1);
            cell.setCellValue(title2[i]);
            cell.setCellStyle(cellStyle);
        }
        // 创建底部标题
        HSSFRow row3 = sheet.createRow(values.length + 3);//创建表格行
        for (int i = 0; i < title3.length; i++) {
            cell = row3.createCell(i);
            cell.setCellValue(title3[i]);
            HSSFCellStyle cellStyleNew = workbook.createCellStyle();
            cellStyleNew.setAlignment(HorizontalAlignment.LEFT);
            cellStyleNew.setWrapText(true);//先设置为自动换行 数据中"\r\n为显示换行"
            cell.setCellStyle(cellStyleNew);
        }
        //合并单元格--标题
        CellRangeAddress region = new CellRangeAddress(0, // first row
                0, // last row
                0, // first column
                19 // last column
        );
        sheet.addMergedRegion(region);
        //合并单元格--序号
        CellRangeAddress region2 = new CellRangeAddress(1, // first row
                2, // last row
                0, // first column
                0 // last column
        );
        sheet.addMergedRegion(region2);
        //合并单元格--务工人员基本信息
        CellRangeAddress region3 = new CellRangeAddress(1, // first row
                1, // last row
                1, // first column
                10 // last column
        );
        sheet.addMergedRegion(region3);
        //合并单元格--入场基本信息
        CellRangeAddress region4 = new CellRangeAddress(1, // first row
                1, // last row
                11, // first column
                19 // last column
        );
        sheet.addMergedRegion(region4);
        //合并单元格-底部
        CellRangeAddress region6 = new CellRangeAddress(values.length + 3, // first row
                values.length + 9, // last row
                0, // first column
                19 // last column
        );
        sheet.addMergedRegion(region6);
        // 创建内容
        for (int i = 0; i < values.length; i++) {
            HSSFRow row = sheet.createRow(i + 3);
            for (int j = 0; j < values[i].length; j++) {
                // 将内容按顺序赋给对应的列对象
                if (j == 7 || j == 8 || j == 9 || j == 16) {
                    HSSFCellStyle cellStyleNew = workbook.createCellStyle();
                    cell = row.createCell(j);
                    CreationHelper createHelper = workbook.getCreationHelper();
                    short dateFormat = createHelper.createDataFormat().getFormat("yyyy/m/d");
                    cellStyleNew.setDataFormat(dateFormat);
                    cellStyleNew.setVerticalAlignment(VerticalAlignment.CENTER);
                    //垂直居中
                    cellStyleNew.setAlignment(HorizontalAlignment.CENTER);
                    cell.setCellStyle(cellStyleNew);
                    if (!"长期".equals(values[i][j])) {
                        try {
                            Long time = HSSFDateUtil.getJavaDate(Double.parseDouble(values[i][j])).getTime();
                            cell.setCellValue(DateUtil.getDateByFangyi(time));
                        } catch (Exception e) {
                            cell.setCellValue("");
                        }
                    } else {
                        cell.setCellValue(values[i][j]);
                    }

                } else {
                    cell = row.createCell(j);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(values[i][j]);
                }

            }
        }
        for (int i = 0; i < title2.length; i++) {
            // 调整每一列宽度
            sheet.autoSizeColumn((short) i);
            // 解决自动设置列宽中文失效的问题
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
        }
        return workbook;
    }

    public static HSSFCellStyle HSSFCellStyle(HSSFWorkbook workbook) {
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        HSSFFont font2 = workbook.createFont();
        font2.setFontName("仿宋_GB2312");
        font2.setFontHeightInPoints((short) 12);  //字体大小
        cellStyle.setFont(font2);
        return cellStyle;
    }

    public static void main(String[] args) {
        List<Map<String, String>> list = new ArrayList();
        // Excel标题
        String[] title = {"劳务务工人员基本信息导入"};
        String[] title1 = {"序号", "务工人员基本信息", "入场基本信息"};
        String[] title2 = {"*姓名", "*联系方式", "*证件号码", "*性别", "民族", "*出生日期", "有效开始时间", "有效截至时间", "地址", "*队伍", "*工班", "*角色", "所属工班长", "*从事工种（岗位）", "*入场时间", "*是否参加培训", "备注", "错误信息"};
        String[] title3 = {"说明：\n" +
                "1.如果务工人员为外籍人员无需填写民族。\n" +
                "2.如果角色选择为工班长，则所属工班长字段无需填写。\n" +
                "3.“*”代表必填项。\n" +
                "4.队伍、工班数据需要先维护到系统中，维护后方可导入成功。\n" +
                "5.从事工种（岗位）数据需要按照工种数据字典中的字段名称填写至表格内，否则会提示导出失败。"};
        // Excel文件名
        String fileName = "人员导入失败信息.xlsx";
        // sheet名
        String sheetName = "人员导入失败信息";
        int titleLength = 19;
        String[][] content = new String[18][titleLength];
        for (int i = CommonConsts.ZERO; i < 18; i++) {
            content[i][0] = i + CommonConsts.ONE + "";
            content[i][1] = i + CommonConsts.ONE + "";
            content[i][2] = i + CommonConsts.ONE + "";
            content[i][3] = i + CommonConsts.ONE + "";
            content[i][4] = i + CommonConsts.ONE + "";
            content[i][5] = i + CommonConsts.ONE + "";
            content[i][6] = 38976 + "";
            content[i][7] = 38976 + "";
            content[i][8] = "长期";
            content[i][9] = i + CommonConsts.ONE + "";
            content[i][10] = i + CommonConsts.ONE + "";
            content[i][11] = i + CommonConsts.ONE + "";
            content[i][12] = i + CommonConsts.ONE + "";
            content[i][13] = i + CommonConsts.ONE + "";
            content[i][14] = i + CommonConsts.ONE + "";
            content[i][15] = 38976 + "";
            content[i][16] = i + CommonConsts.ONE + "";
            content[i][17] = i + CommonConsts.ONE + "";
            content[i][18] = i + CommonConsts.ONE + "";
        }
        HSSFWorkbook hssfWorkbook = ExcelUtil.getHSSFWorkbookInfo(sheetName, title, title1, title2, title3, content, null);
        try {
            hssfWorkbook.write(new FileOutputStream("D:\\新建文件夹\\table6.xls"));
        } catch (IOException e) {
            e.printStackTrace();
        }


//        // Excel标题
//        String[] title = {"施工人员工资表"};
//        String[] title1 = {"单位名称:                                                                结算周期:                                                                金额:"};
//        String[] title2 = {"养老保险","工伤保险","医疗保险","失业保险","合计"};
//        String[] title3 = {"序号","姓名","身份证号","银行卡号","开户行名称","省份","城市","岗位","考勤天数","日工资","实际出勤天数","甲方负担社会保险","满勤","加班费","伙食费","补发工资","本月应发","扣税基数","扣税金额","扣除工资金额","实发金额","签字","备注"};
//        String[] title4 = {"办公室:                                                                财务部:                                                                项目经理:                                                                制表时间"};
//
//        // Excel文件名
//        String fileName =  "sdsd.xlsx";
//        // sheet名
//        String sheetName = "导入失败信息";
//        int titleLength = title.length;
//        String[][] content = new String[list.size()][titleLength];
//        HSSFWorkbook hssfWorkbook = ExcelUtil.getHSSSalaryRegion(CommonConsts.ZERO,sheetName,title,title1,title2,title3,title4,content, null);
//        try {
//            hssfWorkbook.write(new FileOutputStream("D:\\uploadImg\\table6.xls"));
//        } catch (IOException e) {
//            e.printStackTrace();
//        }


//                 //Excel标题
//        String[] title = {"考勤表"};
//        String[] title1 = {"项目名称:                                                                制表时间:                                                                年    月    日    "};
//        String[] title2 = {"序号","姓名","日期","合计"};
//        String[] title3 = {"1","2","3","4","5","6","7","8","9","10","11","12"};
//        String[] title4 = {"注：出勤 √ (按照实际工作时间或完成额定填写) 旷工 × 事假  △ 病假  ○ 公休  ☆"};
//        // Excel文件名
//        String fileName =  "sdsd.xlsx";
//        // sheet名
//        String sheetName = "导入失败信息";
//        int titleLength = title.length;
//        String[][] content = new String[list.size()][titleLength];
//        HSSFWorkbook hssfWorkbook = ExcelUtil.getHSSFWorkbookRegionYear(sheetName,title,title1,title2,title3,title4,content, null);
//        try {
//            hssfWorkbook.write(new FileOutputStream("D:\\uploadImg\\table6.xls"));
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
    }

    /**
     * 工资excle
     *
     * @param sheetName
     * @param title
     * @param title1
     * @param title2
     * @param values
     * @param workbook
     * @return
     */
    public static HSSFWorkbook getHSSPaySalaryRegion(int num, String sheetName, String[] title, String[] title1, String[] title2, String[][] values, HSSFWorkbook workbook) {
        // 创建一个HSSFWorkbook，对应一个Excel文件
        if (workbook == null) {
            workbook = new HSSFWorkbook();
        }
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 在sheet中添加表头第0行
        // HSSFRow row = sheet.createRow(0);
        // 创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 声明列对象
        HSSFCell cell = null;
        // 创建标题
        for (int i = 0; i < title.length; i++) {
            HSSFCellStyle cellStyle1 = workbook.createCellStyle();
            cellStyle1.setAlignment(HorizontalAlignment.CENTER);
            HSSFFont font2 = workbook.createFont();
            font2.setFontName("仿宋_GB2312");
            font2.setFontHeightInPoints((short) 28);  //字体大小
            cellStyle1.setFont(font2);
            HSSFRow row = sheet.createRow(i);//创建表格行
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(cellStyle1);
            row.setHeightInPoints(30);
        }
        // 创建标题
        for (int i = 0; i < title1.length; i++) {
            HSSFRow row = sheet.createRow(i + 1);//创建表格行
            cell = row.createCell(i);
            cell.setCellValue(title1[i]);
            cell.setCellStyle(HSSFCellStyle(workbook));
            row.setHeightInPoints(18);
        }

        // 创建标题
        HSSFRow row3 = sheet.createRow(2);//创建表格行
        for (int i = 0; i < title2.length; i++) {
            cell = row3.createCell(i);
            cell.setCellValue(title2[i]);
            cell.setCellStyle(cellStyle);
        }
        CellRangeAddressList regions = new CellRangeAddressList(3, 3 + values.length, 7, 7);//加载下拉列表内容

        String[] textlist = new String[]{"未发放", "已发放"};

        DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);//数据有效性对象

        HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);

        sheet.addValidationData(data_validation_list);

        //合并单元格--标题
        CellRangeAddress region = new CellRangeAddress(0, // first row
                0, // last row
                0, // first column
                title2.length // last column
        );
        sheet.addMergedRegion(region);
        //合并单元格--标题
        CellRangeAddress region2 = new CellRangeAddress(1, // first row
                1, // last row
                0, // first column
                title2.length // last column
        );
        sheet.addMergedRegion(region2);
        //合并单元格-隐藏信息
        // 创建内容
        for (int i = 0; i < values.length; i++) {
            HSSFRow row = sheet.createRow(i + 3);
            for (int j = 0; j < values[i].length; j++) {
                cell = row.createCell(j);
                // 将内容按顺序赋给对应的列对象
                cell.setCellValue(values[i][j]);
                cell.setCellStyle(cellStyle);
                if (j == title2.length) {
                    sheet.setColumnWidth(j, 0);
                }
            }
        }
        for (int i = 0; i < title.length; i++) {
            // 调整每一列宽度
            sheet.autoSizeColumn((short) i);
            // 解决自动设置列宽中文失效的问题
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
        }
        return workbook;
    }

    public static HSSFWorkbook getHSSFDataSalarybook(String sheetName, String[] title, String[][] values, HSSFWorkbook workbook) {
        // 创建一个HSSFWorkbook，对应一个Excel文件
        if (workbook == null) {
            workbook = new HSSFWorkbook();
        }
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 在sheet中添加表头第0行
        HSSFRow row = sheet.createRow(0);
        // 创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 声明列对象
        HSSFCell cell = null;
        // 创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(cellStyle);
        }
        // 创建内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < values[i].length; j++) {
                cell = row.createCell(j);
                // 将内容按顺序赋给对应的列对象
                cell.setCellValue(values[i][j]);
                cell.setCellStyle(cellStyle);
            }
        }
        for (int i = 0; i < title.length; i++) {
            // 调整每一列宽度
            sheet.autoSizeColumn((short) i);
            // 解决自动设置列宽中文失效的问题
            int colWidth = sheet.getColumnWidth(i) * 17 / 10;
            if (colWidth < 255 * 256) {
                sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
            } else {
                sheet.setColumnWidth(i, 3000);
            }
        }
        return workbook;
    }

    public static HSSFWorkbook getHSSFSalaryRecordDetailbook(String sheetName, String[] title, String[] title1, String[][] values, HSSFWorkbook workbook) {
        // 创建一个HSSFWorkbook，对应一个Excel文件
        if (workbook == null) {
            workbook = new HSSFWorkbook();
        }
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 在sheet中添加表头第0行
        //HSSFRow row = sheet.createRow(0);
        // 创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 声明列对象
        HSSFCell cell = null;
        // 创建标题
        for (int i = 0; i < title.length; i++) {
            HSSFRow row = sheet.createRow(i);//创建表格行
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(HSSFCellStyle(workbook));
            row.setHeightInPoints(18);
        }

        // 创建标题
        HSSFRow row3 = sheet.createRow(1);//创建表格行
        for (int i = 0; i < title1.length; i++) {
            cell = row3.createCell(i);
            cell.setCellValue(title1[i]);
            cell.setCellStyle(cellStyle);
        }
        CellRangeAddress region = new CellRangeAddress(0, // first row
                0, // last row
                0, // first column
                title1.length - 1 // last column
        );
        sheet.addMergedRegion(region);
        // 创建内容
        for (int i = 0; i < values.length; i++) {
            HSSFRow row = sheet.createRow(i + 2);
            for (int j = 0; j < values[i].length; j++) {
                cell = row.createCell(j);
                // 将内容按顺序赋给对应的列对象
                cell.setCellValue(values[i][j]);
                cell.setCellStyle(cellStyle);
            }
        }
        for (int i = 0; i < title.length; i++) {
            // 调整每一列宽度
            sheet.autoSizeColumn((short) i);
            // 解决自动设置列宽中文失效的问题
            int colWidth = sheet.getColumnWidth(i) * 17 / 10;
            if (colWidth < 255 * 256) {
                sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
            } else {
                sheet.setColumnWidth(i, 3000);
            }
        }
        return workbook;
    }

    public static HSSFWorkbook getHSSFAttendanceByMonth(int num, String sheetName, String[] title, String[] title2, String[] title3, String[][] values, HSSFWorkbook workbook) {
        // 创建一个HSSFWorkbook，对应一个Excel文件
        if (workbook == null) {
            workbook = new HSSFWorkbook();
        }
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 在sheet中添加表头第0行
        // HSSFRow row = sheet.createRow(0);
        // 创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 声明列对象
        HSSFCell cell = null;
        // 创建标题
        for (int i = 0; i < title.length; i++) {
            HSSFCellStyle cellStyle1 = workbook.createCellStyle();
            cellStyle1.setAlignment(HorizontalAlignment.CENTER);
            HSSFFont font2 = workbook.createFont();
            font2.setFontName("仿宋_GB2312");
            font2.setFontHeightInPoints((short) 28);  //字体大小
            cellStyle1.setFont(font2);
            HSSFRow row = sheet.createRow(i);//创建表格行
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(cellStyle1);
            row.setHeightInPoints(30);
        }
        CellRangeAddress region = new CellRangeAddress(0, // first row
                0, // last row
                0, // first column
                title3.length-1 // last column
        );
        sheet.addMergedRegion(region);
        // 创建标题
        int not = 0;
        HSSFRow row3 = sheet.createRow(1);//创建表格行
        for (int i = 0; i < title3.length; i++) {
            cell = row3.createCell(not);
            cell.setCellValue(title3[i]);
            cell.setCellStyle(cellStyle);
//            sheet.addMergedRegion(new CellRangeAddress(1, 2, not, not));
            not = not + 1;
        }
        // 创建内容
        for (int i = 0; i < values.length; i++) {
//            HSSFRow row = sheet.createRow(i + 3);
            HSSFRow row = sheet.createRow(i + 2);
            for (int j = 0; j < values[i].length; j++) {
                cell = row.createCell(j);
                // 将内容按顺序赋给对应的列对象
                cell.setCellValue(values[i][j]);
                cell.setCellStyle(cellStyle);
//                if (j == not) {
//                    sheet.setColumnWidth(j, 0);
//                }
            }
        }
        /*int in = 3;
        int start = 0;
        for (int k = 0; k < values.length; k++) {
            String str = values[k][1];
            String project = values[k][2];
            start = k + 3;
            in = start;
            for (int m = 0; m < values.length; m++) {
                if (k != m && str.equals(values[m][1]) && project.equals(values[m][2])) {
                    k++;
                    in++;
                }
            }
            if (start != in && in > start) {
                CellRangeAddress region1 = new CellRangeAddress(start, // first row
                        in, // last row
                        1, // first column
                        1 // last column
                );
                sheet.addMergedRegion(region1);
                CellRangeAddress region2 = new CellRangeAddress(start, // first row
                        in, // last row
                        2, // first column
                        2 // last column
                );
                sheet.addMergedRegion(region2);
                CellRangeAddress region3 = new CellRangeAddress(start, // first row
                        in, // last row
                        3, // first column
                        3 // last column
                );
                sheet.addMergedRegion(region3);
                CellRangeAddress region4 = new CellRangeAddress(start, // first row
                        in, // last row
                        4, // first column
                        4 // last column
                );
                sheet.addMergedRegion(region4);
                CellRangeAddress region5 = new CellRangeAddress(start, // first row
                        in, // last row
                        5, // first column
                        5 // last column
                );
                sheet.addMergedRegion(region5);
                CellRangeAddress region6 = new CellRangeAddress(start, // first row
                        in, // last row
                        6, // first column
                        6 // last column
                );
                sheet.addMergedRegion(region6);
            }

        }*/
        for (int i = 0; i <= title3.length; i++) {
            // 调整每一列宽度
            sheet.autoSizeColumn((short) i);
            // 解决自动设置列宽中文失效的问题
            int colWidth = sheet.getColumnWidth(i) * 25 / 10;
            if (colWidth < 255 * 256) {
                sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 25 / 10);
            } else {
                sheet.setColumnWidth(i, 4000);
            }
        }
        return workbook;
    }

    public static HSSFWorkbook getHSSFLaborWorkAttendanceRecordDay(String sheetName, String[] title, String[][] values, HSSFWorkbook workbook) {
        // 创建一个HSSFWorkbook，对应一个Excel文件
        if (workbook == null) {
            workbook = new HSSFWorkbook();
        }
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 在sheet中添加表头第0行
        HSSFRow row = sheet.createRow(0);
        // 创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 声明列对象
        HSSFCell cell = null;
        // 创建字段
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            HSSFCellStyle cellStyleTitle = workbook.createCellStyle();
            cellStyleTitle.setAlignment(HorizontalAlignment.CENTER);
            cellStyleTitle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
            cellStyleTitle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cellStyleTitle.setBorderBottom(BorderStyle.THIN); //下边框
            cellStyleTitle.setBorderLeft(BorderStyle.THIN);//左边框
            cellStyleTitle.setBorderTop(BorderStyle.THIN);//上边框
            cellStyleTitle.setBorderRight(BorderStyle.THIN);//右边框
            cell.setCellStyle(cellStyleTitle);
        }
        // 创建内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < values[i].length; j++) {
                cell = row.createCell(j);
                // 将内容按顺序赋给对应的列对象
                cell.setCellValue(values[i][j]);
                cell.setCellStyle(cellStyle);
            }
        }
        for (int i = 0; i < title.length; i++) {
            // 调整每一列宽度
            sheet.autoSizeColumn((short) i);
            // 解决自动设置列宽中文失效的问题
            int colWidth = sheet.getColumnWidth(i) * 17 / 10;
            if (colWidth < 255 * 256) {
                sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
            } else {
                sheet.setColumnWidth(i, 3000);
            }
        }
        return workbook;

    }

    public static HSSFWorkbook getHSSPercentageLabor(String sheetName, String[] title, String[] title1, String[] title2, String[][] values, HSSFWorkbook workbook) {
        // 创建一个HSSFWorkbook，对应一个Excel文件
        if (workbook == null) {
            workbook = new HSSFWorkbook();
        }
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 在sheet中添加表头第0行
        // HSSFRow row = sheet.createRow(0);
        // 创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 声明列对象
        HSSFCell cell = null;
        // 创建标题
        for (int i = 0; i < title.length; i++) {
            HSSFCellStyle cellStyle1 = workbook.createCellStyle();
            cellStyle1.setAlignment(HorizontalAlignment.CENTER);
            HSSFFont font2 = workbook.createFont();
            font2.setFontName("仿宋_GB2312");
            font2.setFontHeightInPoints((short) 28);  //字体大小
            cellStyle1.setFont(font2);
            HSSFRow row = sheet.createRow(i);//创建表格行
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(cellStyle1);
            row.setHeightInPoints(30);
        }
        //导出时间
        HSSFRow row1 = sheet.createRow(1);
        for (int i = 0; i < title1.length; i++) {
            cell = row1.createCell(i);
            cell.setCellValue(title1[i]);
            cell.setCellStyle(HSSFCellStyle(workbook));
            row1.setHeightInPoints(18);
        }
        // 创建标题
        HSSFRow row3 = sheet.createRow(2);//创建表格行
        for (int i = 0; i < title2.length; i++) {
            cell = row3.createCell(i);
            cell.setCellValue(title2[i]);
            cell.setCellStyle(cellStyle);
        }

        //合并单元格--标题
        CellRangeAddress region = new CellRangeAddress(0, // first row
                0, // last row
                0, // first column
                title2.length - 1 // last column
        );
        sheet.addMergedRegion(region);
        //合并单元格--标题
        CellRangeAddress region2 = new CellRangeAddress(1, // first row
                1, // last row
                0, // first column
                title2.length - 1 // last column
        );
        sheet.addMergedRegion(region2);
        //合并单元格-隐藏信息
        // 创建内容
        for (int i = 0; i < values.length; i++) {
            HSSFRow row = sheet.createRow(i + 3);
            for (int j = 0; j < values[i].length; j++) {
                cell = row.createCell(j);
                // 将内容按顺序赋给对应的列对象
                cell.setCellValue(values[i][j]);
                cell.setCellStyle(cellStyle);
            }
        }
        for (int i = 0; i < title2.length; i++) {
            // 调整每一列宽度
            sheet.autoSizeColumn((short) i);
            // 解决自动设置列宽中文失效的问题
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
        }
        return workbook;
    }

    public static HSSFWorkbook getHSSFWorkbookFangyi(String sheetName, String[] title, String[][] values, HSSFWorkbook workbook) {
        // 创建一个HSSFWorkbook，对应一个Excel文件
        if (workbook == null) {
            workbook = new HSSFWorkbook();
        }
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 在sheet中添加表头第0行
        HSSFRow row = sheet.createRow(0);
        // 创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 声明列对象
        HSSFCell cell = null;
        // 创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(cellStyle);
        }
        // 创建内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < values[i].length; j++) {
                cell = row.createCell(j);
                // 将内容按顺序赋给对应的列对象
                cell.setCellValue(values[i][j]);
                cell.setCellStyle(cellStyle);
            }
        }
        for (int i = 0; i < title.length; i++) {
            // 调整每一列宽度
            sheet.autoSizeColumn((short) i);
            // 解决自动设置列宽中文失效的问题
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 15);
        }
        return workbook;

    }


    /**
     * 用于导出智能比对OCR识别结果与差异
     * 功能：工资管理-》工资单月度档案-》第三步智能比对
     *
     * @param sheetName
     * @param bigTitle
     * @param smallTitle
     * @param keys
     * @param values
     * @Author xmj
     * @Description 用于导出智能比对OCR识别结果与差异
     * @Date 09:29 2023/2/3
     * @Return org.apache.poi.hssf.usermodel.HSSFWorkbook
     */
    public static HSSFWorkbook getHSSFWorkbookOfflineDifference(String sheetName, String bigTitle, String smallTitle, String[] keys, String[][] values) {
        // 创建一个HSSFWorkbook，对应一个Excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);

        // 声明列对象
        HSSFCell cell = null;
        //创建大标题
        HSSFCellStyle bigCellStyle = workbook.createCellStyle();
        bigCellStyle.setAlignment(HorizontalAlignment.CENTER);
        HSSFFont bigFont = workbook.createFont();
        bigFont.setFontName("仿宋_GB2312");
        bigFont.setFontHeightInPoints((short) 28);  //字体大小
        bigCellStyle.setFont(bigFont);
        HSSFRow bigTitleRow = sheet.createRow(CommonConsts.ZERO);//创建表格行
        cell = bigTitleRow.createCell(CommonConsts.ZERO);
        cell.setCellValue(bigTitle);
        cell.setCellStyle(bigCellStyle);
        bigTitleRow.setHeightInPoints(30);

        //创建小标题
        HSSFCellStyle smallCellStyle = workbook.createCellStyle();
        smallCellStyle.setAlignment(HorizontalAlignment.LEFT);
        HSSFFont smallFont = workbook.createFont();
        smallFont.setFontName("仿宋_GB2312");
        smallFont.setFontHeightInPoints((short) 11);  //字体大小
        smallCellStyle.setFont(smallFont);
        HSSFRow smallTitleRow = sheet.createRow(CommonConsts.ONE);//创建表格行
        cell = smallTitleRow.createCell(CommonConsts.ZERO);
        cell.setCellValue(smallTitle);
        cell.setCellStyle(smallCellStyle);

        // 创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);

        // 在sheet中添加表头第0行
        HSSFRow row = sheet.createRow(CommonConsts.TWO);
        // 创建表字段
        for (int i = 0; i < keys.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(keys[i]);
            cell.setCellStyle(cellStyle);
        }
        // 创建表内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + CommonConsts.THREE);
            for (int j = 0; j < values[i].length; j++) {
                cell = row.createCell(j);
                // 将内容按顺序赋给对应的列对象
                cell.setCellValue(values[i][j]);
                cell.setCellStyle(cellStyle);
            }
        }

        //合并单元格--标题
        CellRangeAddress bigTitleRegion = new CellRangeAddress(
                0, // first row
                0, // last row
                0, // first column
                keys.length - 1 // last column
        );
        sheet.addMergedRegion(bigTitleRegion);
        //合并单元格--标题
        CellRangeAddress smallTitleRegion2 = new CellRangeAddress(
                1, // first row
                1, // last row
                0, // first column
                keys.length - 1 // last column
        );
        sheet.addMergedRegion(smallTitleRegion2);


        for (int i = 0; i < keys.length; i++) {
            // 调整每一列宽度
            sheet.autoSizeColumn((short) i);
            // 解决自动设置列宽中文失效的问题
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 15);
        }
        return workbook;

    }


    /**
     * 用户登录统计导出
     *
     * @param sheetName sheet名称
     * @param bigTitle  大标题
     * @param keys      标题字段
     * @param values    字段值
     * @Author xmj
     * @Description 用户登录统计导出
     * @Date 11:22 2023/3/6
     * @Return org.apache.poi.hssf.usermodel.HSSFWorkbook
     */
    public static HSSFWorkbook getHSSFWorkbookLaborUserLogin(String sheetName, String bigTitle, String[] keys, String[][] values) {
        // 创建一个HSSFWorkbook，对应一个Excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        //隐藏后三行
        sheet.setColumnHidden((short) keys.length - 1, true);
        sheet.setColumnHidden((short) keys.length - 2, true);
        sheet.setColumnHidden((short) keys.length - 3, true);

        // 声明列对象
        HSSFCell cell = null;
        //创建大标题
        HSSFCellStyle bigCellStyle = workbook.createCellStyle();
        bigCellStyle.setAlignment(HorizontalAlignment.CENTER);
        HSSFFont bigFont = workbook.createFont();
        bigFont.setFontName("仿宋_GB2312");
        bigFont.setFontHeightInPoints((short) 18);  //字体大小
        bigCellStyle.setFont(bigFont);
        HSSFRow bigTitleRow = sheet.createRow(CommonConsts.ZERO);//创建表格行
        cell = bigTitleRow.createCell(CommonConsts.ZERO);
        cell.setCellValue(bigTitle);
        cell.setCellStyle(bigCellStyle);
        bigTitleRow.setHeightInPoints(30);

        // 创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);

        // 在sheet中添加表头第0行
        HSSFRow row = sheet.createRow(CommonConsts.ONE);
        // 创建表字段
        for (int i = 0; i < keys.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(keys[i]);
            cell.setCellStyle(cellStyle);
        }
        // 创建表内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + CommonConsts.TWO);
            for (int j = 0; j < values[i].length; j++) {
                cell = row.createCell(j);
                // 将内容按顺序赋给对应的列对象
                cell.setCellValue(values[i][j]);
                cell.setCellStyle(cellStyle);
            }
        }

        //合并单元格--标题
        CellRangeAddress bigTitleRegion = new CellRangeAddress(
                0, // first row
                0, // last row
                0, // first column
                keys.length - 1 // last column
        );
        sheet.addMergedRegion(bigTitleRegion);

        for (int i = 0; i < keys.length; i++) {
            // 调整每一列宽度
            sheet.autoSizeColumn((short) i);
            // 解决自动设置列宽中文失效的问题
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 15);
        }
        return workbook;

    }

    /**
     * 国外工资单模版
     *
     * @param sheetName sheet名称
     * @param bigTitle  大标题
     * @param keys      标题字段
     * @param values    字段值
     * @Author xmj
     * @Description 国外工资单模版
     * @Date 11:22 2023/3/6
     * @Return org.apache.poi.hssf.usermodel.HSSFWorkbook
     */
    public static HSSFWorkbook getHSSFWorkbookSalaryRecordOverseasTheTemplate(String sheetName, String bigTitle, String secondTitle, String thirdTitle, String fourthTitle, String bossSignStr, String[] keys, String[][] values) {
        // 创建一个HSSFWorkbook，对应一个Excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);

        // 声明列对象
        HSSFCell cell = null;
        //创建大标题
        HSSFCellStyle bigCellStyle = workbook.createCellStyle();
        bigCellStyle.setAlignment(HorizontalAlignment.CENTER);
        HSSFFont bigFont = workbook.createFont();
        bigFont.setFontName("仿宋_GB2312");
        bigFont.setFontHeightInPoints((short) 28);  //字体大小
        bigCellStyle.setFont(bigFont);
        HSSFRow bigTitleRow = sheet.createRow(CommonConsts.ZERO);//创建表格行
        cell = bigTitleRow.createCell(CommonConsts.ZERO);
        cell.setCellValue(bigTitle);
        cell.setCellStyle(bigCellStyle);
        bigTitleRow.setHeightInPoints(30);

        //创建第二标题
        HSSFCellStyle secondTitleCellStyle = workbook.createCellStyle();
        secondTitleCellStyle.setAlignment(HorizontalAlignment.LEFT);
        HSSFFont secondTitleFont = workbook.createFont();
        secondTitleFont.setFontName("仿宋_GB2312");
        secondTitleFont.setFontHeightInPoints((short) 11);  //字体大小
        secondTitleFont.setColor(HSSFFont.COLOR_RED);
        secondTitleCellStyle.setFont(secondTitleFont);
        HSSFRow secondTitleRow = sheet.createRow(CommonConsts.ONE);//创建表格行
        cell = secondTitleRow.createCell(CommonConsts.ZERO);
        cell.setCellValue(secondTitle);
        cell.setCellStyle(secondTitleCellStyle);
        secondTitleRow.setHeightInPoints(0);
        //创建第三标题
        HSSFCellStyle thirdTitleCellStyle = workbook.createCellStyle();
        thirdTitleCellStyle.setAlignment(HorizontalAlignment.LEFT);
        HSSFFont thirdTitleFont = workbook.createFont();
        thirdTitleFont.setFontName("仿宋_GB2312");
        thirdTitleFont.setFontHeightInPoints((short) 11);  //字体大小
        thirdTitleCellStyle.setFont(thirdTitleFont);
        HSSFRow thirdTitleRow = sheet.createRow(CommonConsts.TWO);//创建表格行
        cell = thirdTitleRow.createCell(CommonConsts.ZERO);
        cell.setCellValue(thirdTitle);
        cell.setCellStyle(thirdTitleCellStyle);
        thirdTitleRow.setHeightInPoints(13);
        //创建领导签字行
        HSSFCellStyle bossSignStrCellStyle = workbook.createCellStyle();
        bossSignStrCellStyle.setAlignment(HorizontalAlignment.LEFT);
        HSSFFont bossSignStrFont = workbook.createFont();
        bossSignStrFont.setFontName("仿宋_GB2312");
        bossSignStrFont.setFontHeightInPoints((short) 11);  //字体大小
        bossSignStrCellStyle.setFont(bossSignStrFont);
        HSSFRow bossSignStrRow = sheet.createRow((values.length < 1 ? 5 : values.length) + CommonConsts.SIX);//创建表格行
        cell = bossSignStrRow.createCell(CommonConsts.ZERO);
        cell.setCellValue(bossSignStr);
        cell.setCellStyle(bossSignStrCellStyle);
        bossSignStrRow.setHeightInPoints(13);

        // 创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);

        // 在sheet中添加表头第0行
        HSSFRow row = sheet.createRow(CommonConsts.THREE);
        // 创建表字段
        for (int i = 0; i < keys.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(keys[i]);
            cell.setCellStyle(cellStyle);
        }
        // 创建表内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + CommonConsts.FOUR);
            for (int j = 0; j < values[i].length; j++) {
                cell = row.createCell(j);
                // 将内容按顺序赋给对应的列对象
                cell.setCellValue(values[i][j]);
                cell.setCellStyle(cellStyle);
            }

        }

        //合并单元格--大标题
        CellRangeAddress bigTitleRegion = new CellRangeAddress(
                0, // first row
                0, // last row
                0, // first column
                keys.length - 1 // last column
        );
        sheet.addMergedRegion(bigTitleRegion);
        //合并单元格--第二标题
        CellRangeAddress secondTitleRegion = new CellRangeAddress(
                1, // first row
                1, // last row
                0, // first column
                keys.length - 1 // last column
        );
        sheet.addMergedRegion(secondTitleRegion);
        //合并单元格--第三标题
        CellRangeAddress thirdTitleRegion = new CellRangeAddress(
                2, // first row
                2, // last row
                0, // first column
                keys.length - 1 // last column
        );
        sheet.addMergedRegion(thirdTitleRegion);
        //合并单元格--领导签字
        CellRangeAddress bossSignStrRegion = new CellRangeAddress(
                (values.length < 1 ? 5 : values.length) + CommonConsts.SIX, // first row
                (values.length < 1 ? 5 : values.length) + CommonConsts.SIX, // last row
                0, // first column
                keys.length - 1 // last column
        );
        sheet.addMergedRegion(bossSignStrRegion);

        for (int i = 0; i < keys.length; i++) {
            // 调整每一列宽度
            sheet.autoSizeColumn((short) i);
            // 解决自动设置列宽中文失效的问题
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 15);
        }
        return workbook;

    }

    public static HSSFWorkbook getHSSFWorkbookCS(String sheetName, String[] title, String[][] values, HSSFWorkbook workbook) {
        // 创建一个HSSFWorkbook，对应一个Excel文件
        if (workbook == null) {
            workbook = new HSSFWorkbook();
        }
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);

        HSSFRow titleRow = sheet.createRow(0);
        HSSFCell masterCell = titleRow.createCell(4);
        masterCell.setCellValue("证件应上传");
        masterCell.setCellStyle(cellStyle);
        //合并列
        CellRangeAddress region = new CellRangeAddress(0, 0, 4, 6);
        sheet.addMergedRegion(region);

        HSSFCell masterTwoCell = titleRow.createCell(7);
        masterTwoCell.setCellValue("证件已上传");
        masterTwoCell.setCellStyle(cellStyle);
        //合并列
        CellRangeAddress regionTwo = new CellRangeAddress(0, 0, 7, 9);
        sheet.addMergedRegion(regionTwo);

        HSSFCell masterThreeCell = titleRow.createCell(10);
        masterThreeCell.setCellValue("证件未上传");
        masterThreeCell.setCellStyle(cellStyle);
        //合并列
        CellRangeAddress regionThree = new CellRangeAddress(0, 0, 10, 12);
        sheet.addMergedRegion(regionThree);

        // 在sheet中添加表头第0行
        HSSFRow row = sheet.createRow(1);
        // 声明列对象
        HSSFCell cell = null;
        // 创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(cellStyle);
        }
        // 创建内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 2);
            for (int j = 0; j < values[i].length; j++) {
                cell = row.createCell(j);
                // 将内容按顺序赋给对应的列对象
                cell.setCellValue(values[i][j]);
                cell.setCellStyle(cellStyle);
            }
        }
        for (int i = 0; i < title.length; i++) {
            // 调整每一列宽度
            sheet.autoSizeColumn((short) i);
            // 解决自动设置列宽中文失效的问题
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 15);
        }
        return workbook;

    }


    public static void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            fileName = new String(fileName.getBytes(), StandardCharsets.UTF_8);
            response.setContentType("application/octet-stream;charset=utf-8");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
//            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
